package com.css.fxfzypg.modules.maps.service.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.manage.sampling.repository.entity.SamplingEntity;
import com.css.fxfzypg.modules.baseData.entity.RiskEnterpriseData;
import com.css.fxfzypg.modules.earthquakedisasterloss.entity.YpgResultRoad;
import com.css.fxfzypg.modules.earthquakedisasterloss.entity.YpgResultSlope;
import com.css.fxfzypg.modules.earthquakedisasterloss.entity.YpgResultSlopeData;
import com.css.fxfzypg.modules.maps.service.CountLandslideMapService;
import com.css.fxfzypg.modules.rating.thread.BizThreadPoolManage;
import com.css.fxfzypg.util.JdbcUtil;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.util.UUIDGenerator;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author zyt
 * @Date: 2022/10/18
 */
@Service
@Slf4j
public class CountLandslideMapServiceImpl implements CountLandslideMapService {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    SUserService sUserService;

    /**
     * 数据源
     * 10.19.41.111
     */
    @Value("${spring.datasource.url}")
    private String url = "";
    @Value("${spring.datasource.username}")
    private String name = "";
    @Value("${spring.datasource.password}")
    private String pwd = "";

    /**
     * 获取数据库连接
     * SQL语句
     */
    private Connection getConnection() throws Exception {
        //Class.forName("com.mysql.cj.jdbc.Driver");
        Class.forName("org.postgresql.Driver");
        Connection conn = DriverManager.getConnection(url, name, pwd);
        return conn;
    }

    //保存缓冲区内相交部分的道路数据
    @Override
    public void saveResultRoad(String taskId,String roadRange,String dzIntensity) {
//        //查询
//        String wheresql = "(a.result_level='四级' or a.result_level='五级') and";
//        String wheresql1 = "(c.result_level='四级' or c.result_level='五级') and";
//        String sql = "SELECT s.road_name,s.road_type,s.id,st_astext(st_intersection((" +
//                "select st_buffer(st_union(a.geom),0.00000899*"+roadRange+",'join=mitre') from ypg_result_slope a " +
//                "where "+wheresql+" a.task_id='"+taskId+"'),s.geom)) as geomText," +
//                "'"+taskId+"' as task_id FROM " +
//                "(select * from sys_road t where t.road_status='1' and ST_Intersects(t.geom,(" +
//                "select st_buffer(st_union(c.geom),0.00000899*"+roadRange+",'join=mitre') from ypg_result_slope c " +
//                "where "+wheresql1+" c.task_id='"+taskId+"'))) s";
//        List<YpgResultRoad> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgResultRoad.class));
//        if(list!=null && list.size()>0){
//            //保存
//            jdbcTemplate.batchUpdate("insert into ypg_result_road_new (uuid,road_name,road_type,id,geom,task_id) values(?,?,?,?,st_geometryfromtext(?,4490),?)", new BatchPreparedStatementSetter() {
//                @Override
//                public void setValues(PreparedStatement ps, int i) throws SQLException {
//                    ps.setString(1,UUIDGenerator.getUUID());//主键
//                    ps.setString(2,list.get(i).getRoadName());
//                    ps.setString(3,list.get(i).getRoadType());
//                    ps.setString(4,list.get(i).getId());
//                    ps.setString(5,list.get(i).getGeomText());
//                    ps.setString(6,list.get(i).getTaskId());
//                }
//
//                @Override
//                public int getBatchSize() {
//                    return list.size();
//                }
//            });
//        }
        //查询地震滑坡计算结果列表
        String sql0 = "select * from ypg_result_slope_new where task_id = ? and dz_intensity = ?";
        List<YpgResultSlope> ypgResultSlopeList = jdbcTemplate.query(sql0, new BeanPropertyRowMapper<>(YpgResultSlope.class),taskId,dzIntensity);
        if(ypgResultSlopeList!=null && ypgResultSlopeList.size()>0){
            String bufferLayerName4 = null;
            String bufferLayerName5 = null;
            for(YpgResultSlope ypgResultSlope : ypgResultSlopeList){
                if(ypgResultSlope.getExtends1().equals("缓冲区4级")){
                    bufferLayerName4 = ypgResultSlope.getFilePath();
                }else if(ypgResultSlope.getExtends1().equals("缓冲区5级")){
                    bufferLayerName5 = ypgResultSlope.getFilePath();
                }
            }
            if (PlatformObjectUtils.isNotEmpty(bufferLayerName4)) {
                String sql = "SELECT s.road_name,s.road_type,s.id," +
                        "st_astext(st_intersection((select d.smgeometry from "+bufferLayerName4+" d),s.geom)) as geomText,'"+taskId+"' as task_id,'"+dzIntensity+"' as dz_intensity " +
                        "FROM sys_road s,"+bufferLayerName4+" d " +
                        "where s.road_status='1' and ST_Intersects(s.geom,d.smgeometry)";
                System.out.println("缓冲区4相交道路----"+sql);
                //查询
                List<YpgResultRoad> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgResultRoad.class));
                if(list!=null && list.size()>0){
                    //保存
                    jdbcTemplate.batchUpdate("insert into ypg_result_road_new (uuid,road_name,road_type,id,geom,task_id,dz_intensity) values(?,?,?,?,st_geometryfromtext(?,4490),?,?)", new BatchPreparedStatementSetter() {
                        @Override
                        public void setValues(PreparedStatement ps, int i) throws SQLException {
                            ps.setString(1,UUIDGenerator.getUUID());//主键
                            ps.setString(2,list.get(i).getRoadName());
                            ps.setString(3,list.get(i).getRoadType());
                            ps.setString(4,list.get(i).getId());
                            ps.setString(5,list.get(i).getGeomText());
                            ps.setString(6,list.get(i).getTaskId());
                            ps.setString(7,list.get(i).getDzIntensity());//烈度
                        }

                        @Override
                        public int getBatchSize() {
                            return list.size();
                        }
                    });
                }
            }
            if (PlatformObjectUtils.isNotEmpty(bufferLayerName5)) {
                String sql = "SELECT s.road_name,s.road_type,s.id," +
                        "st_astext(st_intersection((select d.smgeometry from "+bufferLayerName5+" d),s.geom)) as geomText,'"+taskId+"' as task_id,'"+dzIntensity+"' as dz_intensity " +
                        "FROM sys_road s,"+bufferLayerName5+" d " +
                        "where s.road_status='1' and ST_Intersects(s.geom,d.smgeometry)";
                System.out.println("缓冲区5相交道路----"+sql);
                //查询
                List<YpgResultRoad> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgResultRoad.class));
                if(list!=null && list.size()>0){
                    //保存
                    jdbcTemplate.batchUpdate("insert into ypg_result_road_new (uuid,road_name,road_type,id,geom,task_id,dz_intensity) values(?,?,?,?,st_geometryfromtext(?,4490),?,?)", new BatchPreparedStatementSetter() {
                        @Override
                        public void setValues(PreparedStatement ps, int i) throws SQLException {
                            ps.setString(1,UUIDGenerator.getUUID());//主键
                            ps.setString(2,list.get(i).getRoadName());
                            ps.setString(3,list.get(i).getRoadType());
                            ps.setString(4,list.get(i).getId());
                            ps.setString(5,list.get(i).getGeomText());
                            ps.setString(6,list.get(i).getTaskId());
                            ps.setString(7,list.get(i).getDzIntensity());//烈度
                        }

                        @Override
                        public int getBatchSize() {
                            return list.size();
                        }
                    });
                }
            }

            //删除缓冲区临时表
            if (PlatformObjectUtils.isNotEmpty(bufferLayerName4)) {
                //删除
//                String sql = "drop table " + bufferLayerName4;
                String sql = "select DropGeometryTable('"+bufferLayerName4+"')";//空间表删除
                jdbcTemplate.execute(sql);
            }
            if (PlatformObjectUtils.isNotEmpty(bufferLayerName5)) {
                //删除
//                String sql = "drop table " + bufferLayerName5;
                String sql = "select DropGeometryTable('"+bufferLayerName5+"')";//空间表删除
                jdbcTemplate.execute(sql);
            }
        }
    }

    //根据任务ID、烈度查询缓冲区内相交部分的道路数据
    @Override
    public List<YpgResultRoad> getYpgResultRoadByTaskId(String taskId,String dzIntensity) {
        String sql="select *,ST_AsText(geom) geomText from ypg_result_road_new where task_id = ? and dz_intensity = ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgResultRoad.class),taskId,dzIntensity);
    }

    //根据任务ID、烈度查询缓冲区内相交部分的道路数据统计结果
    @Override
    public List<YpgResultRoad> getYpgResultRoadListByTaskId(String taskId,String dzIntensity) {
        String sql="SELECT road_type,road_name FROM ypg_result_road_new " +
                "where task_id= ? and dz_intensity = ? " +
                "group by road_type,road_name " +
                "order by road_type,road_name";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgResultRoad.class),taskId,dzIntensity);
    }

    @Override
    public List<YpgResultRoad> getYpgResultRoadListByTaskGeom(String taskId,String dzIntensity) {
        String sql = "SELECT road_type,road_name,dz_intensity FROM ypg_result_road_new t " +
                "WHERE task_id= ?  and " +
                "ST_Intersects(t.geom,(select geom from ypg_preassess_task where id= ? and  del_flag='0')) ";
        if(PlatformObjectUtils.isNotEmpty(dzIntensity)){
            sql+=" and dz_intensity = ? ";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgResultRoad.class),taskId,dzIntensity,taskId);
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgResultRoad.class),taskId,taskId);

    }

    //根据任务ID删除缓冲区内相交部分的道路数据
    @Override
    public int delYpgResultRoad(String taskId) {
        String sql="delete from ypg_result_road_new where task_id = ?";
        return jdbcTemplate.update(sql, taskId);
    }

    //根据任务ID、烈度删除缓冲区内相交部分的道路数据
    @Override
    public int delYpgResultRoadIntensity(String taskId,String dzIntensity) {
        String sql="delete from ypg_result_road_new where task_id = ? and dz_intensity = ?";
        return jdbcTemplate.update(sql, taskId,dzIntensity);
    }

    //根据任务ID查询滑坡计算结果网格
    @Override
    public List<YpgResultSlopeData> getYpgResultSlopeByTaskId(String taskId) {
        String sql="select *,ST_AsText(geom) geom from ypg_result_slope where task_id = ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgResultSlopeData.class),taskId);
    }

    //根据任务ID查询滑坡缓冲区
    @Override
    public List<YpgResultSlopeData> getYpgResultSlopeBufferByTaskId(String taskId) {
        //用米转为为度 *180/(6371000*PI) 即0.00000899
        String sql="select result_level,st_astext(st_buffer(st_union(geom),0.00000899*road_range,'join=mitre')) as geom " +
                "from ypg_result_slope " +
                "where (result_level='四级' or result_level='五级') and task_id=? " +
                "group by result_level,road_range";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgResultSlopeData.class),taskId);
    }

    //根据任务ID、烈度查询计算结果图层信息列表（图层数据类型：最终计算、分级矢量化、缓冲区4级、缓冲区5级）
    @Override
    public List<YpgResultSlope> getYpgResultSlopeNewByTaskId(String taskId,String dzIntensity) {
        String sql="select * from ypg_result_slope_new where task_id = ? and dz_intensity = ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgResultSlope.class),taskId,dzIntensity);
    }

    //根据任务ID查询全部烈度计算结果列表
    @Override
    public List<YpgResultSlope> getYpgResultSlopeNewIntensityList(String taskId) {
        String sql = "select * from ypg_result_slope_new where task_id = ? order by cast(dz_intensity as int)";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgResultSlope.class),taskId);
    }
}
